package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.management.Query;
import java.sql.SQLException;
import java.util.List;

public class CourseContentDaoImpl implements CourseContentDao {
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {

        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写SQL
            String sql = "SELECT \n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "STATUS\n" +
                    "FROM course_section WHERE course_id = ?";
            //3、执行查询
            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);

            //4、根据章节ID查询课时信息
            for(Course_Section section :sectionList){
                //调用方法 获取章节对应的课时
                List<Course_Lesson> lessonList = findLessonBySectionId(section.getId());

                //将课时数据封装到章节数据中
                section.setCourse_lessonList(lessonList);
            }
            return sectionList;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }


    }


    /**
     * 根据章节id查询课时信息
     * @param sectionId
     * @return
     */
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT \n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "STATUS\n" +
                    "FROM course_lesson WHERE course_id = ?";

            List<Course_Lesson> lessonList = qr.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);

            return lessonList;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }


    }

    @Override
    public Course findCourseByCourseId(int courseId) {

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "select id,course_name from course where id = ?";

            Course course = qr.query(sql, new BeanHandler<Course>(Course.class), courseId);

            return course;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    //保存章节信息
    @Override
    public int saveSection(Course_Section section) {

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "INSERT INTO course_section(\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "STATUS,\n" +
                    "create_time,\n" +
                    "update_time\n" +
                    ")VALUES(?,?,?,?,?,?,?)";

            Object[] param = {section.getCourse_id(),section.getSection_name(),
            section.getDescription(),section.getOrder_num(),section.getStatus(),
            section.getCreate_time(),section.getUpdate_time()};

            int i = qr.update(sql, param);

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }


    }

    @Override
    public int updateSection(Course_Section section) {

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "UPDATE course_section SET\n" +
                    "section_name = ?,\n" +
                    "description = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ?\n" +
                    "WHERE id = ?";

            Object[] param = {section.getSection_name(),section.getDescription(),
            section.getOrder_num(),section.getUpdate_time(),section.getId()};

            int i = qr.update(sql, param);

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }

    @Override
    public int updateSectionStatus(int id, int status) {

        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            String sql = "update course_section set status = ?,update_time = ? where id = ?";

            Object[] param = {status, DateUtils.getDateFormart(), id};

            int i = qr.update(sql, param);

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 保存课时
     * @param lesson
     * @return
     */
    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql语句
            String sql = "INSERT INTO course_lesson\n" +
                    "(\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num,\n" +
                    "STATUS,\n" +
                    "create_time,\n" +
                    "update_time\n" +
                    ")VALUES(?,?,?,?,?,?,?,?,?)";

            //3、准备参数
            Object[] params = {lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),
            lesson.getDuration(),lesson.getIs_free(),lesson.getOrderNum(),
            lesson.getStatus(),lesson.getCreate_time(),lesson.getUpdate_time()};

            //4、执行插入
            int i = qr.update(sql, params);

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }

    @Override
    public int updateLesson(Course_Lesson lesson) {

        try {
            //创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //sql语句
            String sql = "UPDATE course_lesson SET\n" +
                    "section_id = ?,\n" +
                    "theme = ?,\n" +
                    "duration = ?,\n" +
                    "is_free = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ? \n" +
                    "WHERE id = ?";

            Object[] params = {lesson.getSection_id(),lesson.getTheme(),lesson.getDuration(),
            lesson.getIs_free(),lesson.getOrderNum(),lesson.getUpdate_time(),
            lesson.getId()};

            int i = qr.update(sql, params);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }

    }
}
